package com.frank.jpaBatchSave.service;

import com.alibaba.fastjson.JSON;
import com.frank.jpaBatchSave.entity.Student;
import com.frank.jpaBatchSave.entity.User;
import com.frank.jpaBatchSave.repository.Student1Repository;
import com.frank.shardingJdbcMybatis.entity.Address;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ObjectUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;

/**
 * @author 小石潭记
 * @date 2020/10/4 17:03
 * @Description: ${todo}
 */
@Service
@Transactional
@Slf4j
public class UserService {

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private Student1Repository student1Repository;

    @Transactional
    public void updateStudent() {
        /*Student student = student1Repository.findById(1).get();
        student.setAddress("重庆-任家镇");
        student1Repository.save(student);*/
        student1Repository.updateAddress("重庆-任家镇", 1);
    }

    public Object find() {
        /*Query query = entityManager.createNativeQuery("select * from user", User.class);
//        query.setParameter(1, "cxb");
        List resultList = query.getResultList();
        return resultList;*/
        // 查找
        /*User user = entityManager.find(User.class, 1L);
        user.setEmail("810464826@qq.com");
        // 修改上面查询出来的对象
        return entityManager.merge(user);*/
        // 新增
        /*User user = new User();
        user.setNickName("德玛西亚");
        user.setPassWord("123");
        user.setUserInfo("上单盖伦");
        return entityManager.merge(user);*/
        // 删除 remove()方法不能移除游离对象，只能移除持久化对象
        /*User user = entityManager.find(User.class, 3126L);
        User reference = entityManager.getReference(User.class, 3126L);
        System.out.println(reference);
        entityManager.remove(user);
        return "删除成功";*/
        // 自定义sql操作数据库
        /*Query query = entityManager.createNativeQuery("delete from user u where u.id = 3130");
        int result =query.executeUpdate(); //影响的记录数
        return result;*/
        // 引用占位符的前后不允许有空格。根据指定的条件查询
        Query query = entityManager.createNativeQuery("select * from user p where p.nick_name =?1");
        query.setParameter(1,"小石潭记");
        return query.getResultList();
    }

    /**
     * 批量插入
     *
     * @param list 实体类集合
     * @param <T>  表对应的实体类
     */
    public <T> void batchInsert(List<T> list) {
        if (!ObjectUtils.isEmpty(list)) {
            for (int i = 0; i < list.size(); i++) {
                entityManager.persist(list.get(i));
                if (i % 50 == 0) {
                    entityManager.flush();
                    entityManager.clear();
                }
            }
            entityManager.flush();
            entityManager.clear();
        }
    }

    /**
     * 批量更新
     *
     * @param list 实体类集合
     * @param <T>  表对应的实体类
     */
    public <T> void batchUpdate(List<T> list) {
        if (!ObjectUtils.isEmpty(list)) {
            for (int i = 0; i < list.size(); i++) {
                entityManager.merge(list.get(i));
                if (i % 50 == 0) {
                    entityManager.flush();
                    entityManager.clear();
                }
            }
            entityManager.flush();
            entityManager.clear();
        }
    }
    // 这种方式存在sql注入的风险
    public void saveBatch(List<User> list) {
        /*String sql="insert into user " +
                "(user_name)" +
                " values (?)";

        List<Object[]> batchArgs=new ArrayList<Object[]>();

        for (int i = 0; i < list.size(); i++) {
            batchArgs.add(new Object[]{list.get(i)});
        }
        jdbcTemplate.batchUpdate(sql, batchArgs);*/


        StringBuilder insert = new StringBuilder("INSERT INTO `user` (`user_name`, `pass_word`, `nick_name`," +
                "`email`) VALUES ");
        for (int i = 0; i < list.size(); i++) {
            insert.append("(")
                    .append("'").append(list.get(i).getUserName()).append("'").append(",")
                    .append("'").append(list.get(i).getPassWord()).append("'").append(",")
                    .append("'").append(list.get(i).getNickName()).append("'").append(",")
                    .append("'").append(list.get(i).getEmail()).append("'")
                    .append(")");
            if (i < list.size() - 1) {
                insert.append(",");
            }
        }
        String sql = (String) JSON.toJSON(insert);
        log.info("SQL语句:{}", JSON.toJSON(insert));
        try {
            jdbcTemplate.execute(sql);
        } catch (Exception e) {
            log.error("sql解析错误,{}", e.getMessage());
        }

    }

    /**
     * 应该使用PrepareStatement，这样拼接的sql才不会有sql注入的风险
     * @param list
     */
    public void saveBatchUser(List<User> list) {
        long start = System.currentTimeMillis();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            StringBuilder insert = new StringBuilder("insert into `user` (" +
                    "`user_name`, `pass_word`, `nick_name`, `email`) values ");
            String placeholder = "(?, ?, ?, ?),";
            // 拼装sql，values(?,?...),(?,?...)
            list.stream().forEach( x-> insert.append(placeholder));
            // 去掉最后一个，
            String insertSql = insert.substring(0, insert.length() - 1);
            connection = jdbcTemplate.getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(insertSql);
            int userIndex = 0;
            // 这里的5是每个value里面的?的个数
            for (int i = 1; i <= list.size() * 4; userIndex++) {
                preparedStatement.setString(i++, list.get(userIndex).getUserName());
                preparedStatement.setString(i++, list.get(userIndex).getPassWord());
                preparedStatement.setString(i++, list.get(userIndex).getNickName());
                preparedStatement.setString(i++, list.get(userIndex).getEmail());
            }
            // 返回true是查询，返回false是插入或者更新
//            boolean execute = preparedStatement.execute();
//            log.info("返回结果,{},耗时{}ms", execute, System.currentTimeMillis() - start);
            int count = preparedStatement.executeUpdate();
            log.info("插入的数据数量,{},耗时{}ms", count, System.currentTimeMillis() - start);
        } catch (Exception e) {
            log.error("插入数据失败！{}", e.getMessage());
        } finally {
            try {
                // 最后一定要关闭，不然会有内存泄漏风险
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }


    }

    /**
     * 将finally里面的代码使用try resource方式简化
     * @param list
     */
    public void saveBatchUser1(List<User> list) {
        StringBuilder insert = new StringBuilder("insert into `user` (" +
                "`user_name`, `pass_word`, `nick_name`, `email`) values ");
        String placeholder = "(?, ?, ?, ?),";
        // 拼装sql，values(?,?...),(?,?...)
        list.stream().forEach( x-> insert.append(placeholder));
        // 去掉最后一个，
        String insertSql = insert.substring(0, insert.length() - 1);
        long start = System.currentTimeMillis();
        try (Connection connection = jdbcTemplate.getDataSource().getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(insertSql)){
            int userIndex = 0;
            // 这里的5是每个value里面的?的个数
            for (int i = 1; i <= list.size() * 4; userIndex++) {
                preparedStatement.setString(i++, list.get(userIndex).getUserName());
                preparedStatement.setString(i++, list.get(userIndex).getPassWord());
                preparedStatement.setString(i++, list.get(userIndex).getNickName());
                preparedStatement.setString(i++, list.get(userIndex).getEmail());
            }
            // 返回true是查询，返回false是插入或者更新
//            boolean execute = preparedStatement.execute();
//            log.info("返回结果,{},耗时{}ms", execute, System.currentTimeMillis() - start);
            int count = preparedStatement.executeUpdate();
            log.info("插入的数据数量,{},耗时{}ms", count, System.currentTimeMillis() - start);
        } catch (Exception e) {
            log.error("插入数据失败！{}", e.getMessage());
        }
    }
}
